#!/usr/bin/env python3
"""
生成商品信息Excel文件
包含1000条真实的商品信息，涵盖多个类别
"""
import pandas as pd
import numpy as np
import random
from datetime import datetime, timedelta
import string

# 商品分类和品牌数据
PRODUCT_CATEGORIES = {
    "电子产品": {
        "subcategories": ["手机", "笔记本电脑", "平板电脑", "智能手表", "耳机", "音响", "相机", "游戏机"],
        "brands": ["苹果", "华为", "小米", "三星", "联想", "戴尔", "惠普", "索尼", "佳能", "尼康", "任天堂", "微软"],
        "features": ["高清", "智能", "便携", "防水", "无线", "蓝牙", "快充", "长续航", "高性能", "轻薄"]
    },
    "服装鞋帽": {
        "subcategories": ["T恤", "衬衫", "裤子", "裙子", "外套", "鞋子", "包包", "配饰"],
        "brands": ["优衣库", "ZARA", "H&M", "耐克", "阿迪达斯", "李宁", "安踏", " Coach", "LV", "Gucci"],
        "features": ["纯棉", "透气", "修身", "休闲", "商务", "运动", "时尚", "舒适", "防皱", "弹性"]
    },
    "家居用品": {
        "subcategories": ["家具", "家纺", "厨具", "收纳", "装饰", "清洁", "卫浴", "照明"],
        "brands": ["宜家", "无印良品", "顾家", "全友", "九牧", "欧普", "苏泊尔", "美的", "格力", "海尔"],
        "features": ["环保", "实用", "简约", "多功能", "易清洁", "耐用", "安全", "节能", "静音", "智能"]
    },
    "美妆护肤": {
        "subcategories": ["护肤品", "彩妆", "香水", "个人护理", "美容工具", "面膜", "精华", "口红"],
        "brands": ["兰蔻", "雅诗兰黛", "迪奥", "香奈儿", "SK-II", "欧莱雅", "资生堂", "玉兰油", "卡姿兰", "完美日记"],
        "features": ["保湿", "美白", "抗衰老", "控油", "温和", "天然", "有机", "无添加", "长效", "滋润"]
    },
    "食品饮料": {
        "subcategories": ["零食", "饮料", "粮油", "调味品", "保健品", "茶叶", "酒类", "生鲜"],
        "brands": ["三只松鼠", "良品铺子", "百事", "可口可乐", "中粮", "金龙鱼", "茅台", "五粮液", "康师傅", "统一"],
        "features": ["美味", "健康", "营养", "有机", "无糖", "低脂", "天然", "新鲜", "传统工艺", "现代科技"]
    },
    "运动户外": {
        "subcategories": ["运动服装", "运动鞋", "健身器材", "户外装备", "球类", "运动配件", "游泳用品", "瑜伽用品"],
        "brands": ["耐克", "阿迪达斯", "安踏", "李宁", "迪卡侬", "Under Armour", "New Balance", "彪马", "斐乐", "斯凯奇"],
        "features": ["透气", "速干", "防滑", "支撑", "缓冲", "轻便", "耐磨", "弹性", "专业", "多功能"]
    },
    "图书文具": {
        "subcategories": ["图书", "文具", "办公用品", "教辅", "杂志", "报纸", "电子书", "音像制品"],
        "brands": ["人民文学", "商务印书馆", "三联书店", "新华书店", "得力", "晨光", "齐心", "广博", "英雄", "派克"],
        "features": ["正版", "精装", "平装", "畅销", "经典", "新品", "限量", "签名版", "收藏版", "教学用"]
    },
    "母婴用品": {
        "subcategories": ["婴儿用品", "孕妇用品", "玩具", "童装", "奶粉", "辅食", "纸尿裤", "洗护用品"],
        "brands": ["帮宝适", "花王", "美素佳儿", "惠氏", "雅培", "费雪", "乐高", "好孩子", "十月结晶", "贝亲"],
        "features": ["安全", "柔软", "透气", "无刺激", "营养", "益智", "环保", "可水洗", "抗菌", "医用级"]
    }
}

# 生成随机商品数据
def generate_product_data(num_products=1000):
    products = []
    
    # 生成商品编号前缀
    def generate_product_id(category, index):
        category_code = {
            "电子产品": "EL",
            "服装鞋帽": "CL", 
            "家居用品": "HO",
            "美妆护肤": "BE",
            "食品饮料": "FD",
            "运动户外": "SP",
            "图书文具": "BO",
            "母婴用品": "BA"
        }
        return f"{category_code.get(category, 'OT')}{str(index+1).zfill(6)}"
    
    # 生成随机价格
    def generate_price(category):
        price_ranges = {
            "电子产品": (500, 15000),
            "服装鞋帽": (50, 2000),
            "家居用品": (30, 5000),
            "美妆护肤": (20, 2000),
            "食品饮料": (5, 500),
            "运动户外": (80, 3000),
            "图书文具": (10, 500),
            "母婴用品": (15, 1500)
        }
        min_price, max_price = price_ranges.get(category, (10, 1000))
        return round(random.uniform(min_price, max_price), 2)
    
    # 生成库存数量
    def generate_stock():
        return random.randint(0, 9999)
    
    # 生成商品状态
    def generate_status():
        statuses = ["在售", "预售", "缺货", "下架"]
        weights = [0.7, 0.15, 0.1, 0.05]  # 在售概率最高
        return random.choices(statuses, weights=weights)[0]
    
    # 生成商品评分
    def generate_rating():
        return round(random.uniform(3.0, 5.0), 1)
    
    # 生成销量
    def generate_sales():
        return random.randint(0, 10000)
    
    # 生成重量(kg)
    def generate_weight(category):
        weight_ranges = {
            "电子产品": (0.1, 5.0),
            "服装鞋帽": (0.05, 2.0),
            "家居用品": (0.5, 50.0),
            "美妆护肤": (0.02, 1.0),
            "食品饮料": (0.05, 10.0),
            "运动户外": (0.1, 10.0),
            "图书文具": (0.1, 3.0),
            "母婴用品": (0.05, 5.0)
        }
        min_weight, max_weight = weight_ranges.get(category, (0.1, 5.0))
        return round(random.uniform(min_weight, max_weight), 2)
    
    # 生成上架时间
    def generate_create_time():
        start_date = datetime(2023, 1, 1)
        end_date = datetime(2024, 12, 31)
        random_date = start_date + timedelta(days=random.randint(0, (end_date - start_date).days))
        return random_date.strftime("%Y-%m-%d %H:%M:%S")
    
    # 生成商品描述
    def generate_description(category, subcategory, features):
        templates = [
            f"高品质{subcategory}，采用优质材料制作，具有{random.choice(features)}等特性",
            f"精选{subcategory}，设计精良，{random.choice(features)}，值得信赖",
            f"专业{subcategory}，{random.choice(features)}，品质保证",
            f"新款{subcategory}上市，{random.choice(features)}，深受消费者喜爱",
            f"热销{subcategory}，{random.choice(features)}，性价比超高"
        ]
        return random.choice(templates)
    
    # 生成1000条商品数据
    for i in range(num_products):
        # 随机选择商品分类
        category = random.choice(list(PRODUCT_CATEGORIES.keys()))
        category_info = PRODUCT_CATEGORIES[category]
        
        # 随机选择子分类和品牌
        subcategory = random.choice(category_info["subcategories"])
        brand = random.choice(category_info["brands"])
        features = category_info["features"]
        
        # 生成商品名称
        feature = random.choice(features)
        product_name = f"{brand} {feature} {subcategory}"
        
        # 生成其他字段
        product_id = generate_product_id(category, i)
        price = generate_price(category)
        stock = generate_stock()
        status = generate_status()
        rating = generate_rating()
        sales = generate_sales()
        weight = generate_weight(category)
        create_time = generate_create_time()
        description = generate_description(category, subcategory, features)
        
        product = {
            "商品编号": product_id,
            "商品名称": product_name,
            "商品分类": category,
            "子分类": subcategory,
            "品牌": brand,
            "价格(元)": price,
            "库存数量": stock,
            "商品状态": status,
            "用户评分": rating,
            "销量": sales,
            "重量(kg)": weight,
            "上架时间": create_time,
            "商品描述": description
        }
        
        products.append(product)
    
    return products

# 创建Excel文件
def create_product_excel():
    print("正在生成商品数据...")
    
    # 生成商品数据
    products = generate_product_data(1000)
    
    # 创建DataFrame
    df = pd.DataFrame(products)
    
    # 按商品编号排序
    df = df.sort_values("商品编号")
    
    # 保存到Excel文件
    output_file = "data/商品信息表.xlsx"
    df.to_excel(output_file, index=False, engine='openpyxl')
    
    print(f"Excel文件已生成: {output_file}")
    print(f"共生成 {len(df)} 条商品记录")
    
    # 显示统计信息
    print("\n商品分类统计:")
    category_stats = df['商品分类'].value_counts()
    for category, count in category_stats.items():
        print(f"  {category}: {count} 条")
    
    print(f"\n价格范围: {df['价格(元)'].min():.2f} - {df['价格(元)'].max():.2f} 元")
    print(f"平均价格: {df['价格(元)'].mean():.2f} 元")
    print(f"总库存: {df['库存数量'].sum()} 件")
    print(f"总销量: {df['销量'].sum()} 件")
    
    return output_file

if __name__ == "__main__":
    create_product_excel()